<!DOCTYPE html>
<html lang="zh-CN">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>地区数据转换工具 - JSON转PostgreSQL</title>
    <style>
        * {
            margin: 0;
            padding: 0;
            box-sizing: border-box;
        }

        body {
            font-family: 'Microsoft YaHei', Arial, sans-serif;
            background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
            min-height: 100vh;
            padding: 20px;
        }

        .container {
            max-width: 1200px;
            margin: 0 auto;
            background: white;
            border-radius: 20px;
            box-shadow: 0 20px 60px rgba(0,0,0,0.3);
            overflow: hidden;
        }

        .header {
            background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
            color: white;
            padding: 30px;
            text-align: center;
        }

        .header h1 {
            font-size: 32px;
            margin-bottom: 10px;
        }

        .header p {
            font-size: 16px;
            opacity: 0.9;
        }

        .content {
            padding: 30px;
        }

        .step {
            margin-bottom: 30px;
            padding: 20px;
            background: #f7f9fc;
            border-radius: 10px;
            border-left: 4px solid #667eea;
        }

        .step-title {
            font-size: 20px;
            font-weight: bold;
            color: #667eea;
            margin-bottom: 15px;
            display: flex;
            align-items: center;
        }

        .step-number {
            background: #667eea;
            color: white;
            width: 32px;
            height: 32px;
            border-radius: 50%;
            display: flex;
            align-items: center;
            justify-content: center;
            margin-right: 10px;
            font-size: 18px;
        }

        .input-group {
            margin: 15px 0;
        }

        .input-group label {
            display: block;
            margin-bottom: 8px;
            font-weight: bold;
            color: #333;
        }

        textarea {
            width: 100%;
            min-height: 200px;
            padding: 15px;
            border: 2px solid #e0e0e0;
            border-radius: 8px;
            font-family: 'Consolas', 'Courier New', monospace;
            font-size: 14px;
            resize: vertical;
            transition: border-color 0.3s;
        }

        textarea:focus {
            outline: none;
            border-color: #667eea;
        }

        .button-group {
            display: flex;
            gap: 15px;
            margin-top: 20px;
        }

        button {
            flex: 1;
            padding: 15px 30px;
            font-size: 16px;
            font-weight: bold;
            border: none;
            border-radius: 8px;
            cursor: pointer;
            transition: all 0.3s;
            display: flex;
            align-items: center;
            justify-content: center;
            gap: 8px;
        }

        .btn-primary {
            background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
            color: white;
        }

        .btn-primary:hover {
            transform: translateY(-2px);
            box-shadow: 0 5px 15px rgba(102, 126, 234, 0.4);
        }

        .btn-secondary {
            background: #e0e0e0;
            color: #333;
        }

        .btn-secondary:hover {
            background: #d0d0d0;
        }

        .btn-success {
            background: #4caf50;
            color: white;
        }

        .btn-success:hover {
            background: #45a049;
            transform: translateY(-2px);
            box-shadow: 0 5px 15px rgba(76, 175, 80, 0.4);
        }

        .stats {
            display: grid;
            grid-template-columns: repeat(auto-fit, minmax(200px, 1fr));
            gap: 15px;
            margin: 20px 0;
        }

        .stat-card {
            background: white;
            padding: 20px;
            border-radius: 10px;
            border: 2px solid #e0e0e0;
            text-align: center;
        }

        .stat-number {
            font-size: 32px;
            font-weight: bold;
            color: #667eea;
            margin-bottom: 5px;
        }

        .stat-label {
            color: #666;
            font-size: 14px;
        }

        .alert {
            padding: 15px;
            border-radius: 8px;
            margin: 15px 0;
        }

        .alert-success {
            background: #d4edda;
            border: 1px solid #c3e6cb;
            color: #155724;
        }

        .alert-error {
            background: #f8d7da;
            border: 1px solid #f5c6cb;
            color: #721c24;
        }

        .alert-info {
            background: #d1ecf1;
            border: 1px solid #bee5eb;
            color: #0c5460;
        }

        .quick-links {
            background: #fff3cd;
            border: 1px solid #ffeaa7;
            padding: 20px;
            border-radius: 10px;
            margin: 20px 0;
        }

        .quick-links h3 {
            color: #856404;
            margin-bottom: 15px;
        }

        .quick-links a {
            color: #667eea;
            text-decoration: none;
            display: block;
            padding: 8px 0;
            border-bottom: 1px solid #ffeaa7;
        }

        .quick-links a:hover {
            color: #764ba2;
            padding-left: 10px;
            transition: all 0.3s;
        }

        .quick-links a:last-child {
            border-bottom: none;
        }

        code {
            background: #f4f4f4;
            padding: 2px 6px;
            border-radius: 3px;
            font-family: 'Consolas', monospace;
        }

        .loading {
            display: none;
            text-align: center;
            padding: 20px;
        }

        .loading.active {
            display: block;
        }

        .spinner {
            border: 4px solid #f3f3f3;
            border-top: 4px solid #667eea;
            border-radius: 50%;
            width: 40px;
            height: 40px;
            animation: spin 1s linear infinite;
            margin: 0 auto 10px;
        }

        @keyframes spin {
            0% { transform: rotate(0deg); }
            100% { transform: rotate(360deg); }
        }
    </style>
</head>
<body>
    <div class="container">
        <div class="header">
            <h1>🗺️ 地区数据转换工具</h1>
            <p>将JSON格式的中国行政区划数据转换为PostgreSQL格式的SQL</p>
        </div>

        <div class="content">
            <!-- 快速链接 -->
            <div class="quick-links">
                <h3>📦 数据源推荐</h3>
                <a href="https://raw.githubusercontent.com/modood/Administrative-divisions-of-China/master/dist/pca-code.json" target="_blank">
                    🔗 GitHub - 最新完整数据（推荐）
                </a>
                <a href="https://github.com/modood/Administrative-divisions-of-China" target="_blank">
                    📚 数据源项目主页
                </a>
                <a href="http://www.stats.gov.cn/sj/tjbz/tjyqhdmhcxhfdm/" target="_blank">
                    🏛️ 国家统计局官网
                </a>
            </div>

            <!-- 步骤1：粘贴JSON -->
            <div class="step">
                <div class="step-title">
                    <span class="step-number">1</span>
                    粘贴JSON数据
                </div>
                <div class="alert alert-info">
                    <strong>📋 支持两种数据格式：</strong><br>
                    
                    <strong>格式一：树形结构（GitHub格式）</strong><br>
                    <code>[{"code":"11","name":"北京市","children":[...]}]</code><br>
                    
                    <strong>格式二：扁平键值对</strong><br>
                    <code>{"110000":"北京市","120000":"天津市",...}</code><br>
                    
                    <small>
                        • 工具会自动识别格式类型<br>
                        • 树形结构支持嵌套的children<br>
                        • 扁平结构根据代码长度判断层级<br>
                        • 不确定？点击"加载示例数据"查看树形格式
                    </small>
                </div>
                <div class="input-group">
                    <label>从上面的链接复制JSON数据，粘贴到下面：</label>
                    <textarea id="jsonInput" placeholder='粘贴JSON数据，格式如：{"110000":"北京市","120000":"天津市",...}'></textarea>
                </div>
                <div class="button-group">
                    <button class="btn-primary" onclick="parseJSON()">
                        🔄 解析并转换
                    </button>
                    <button class="btn-secondary" onclick="loadSample()">
                        📝 加载示例数据
                    </button>
                    <button class="btn-secondary" onclick="clearInput()">
                        🗑️ 清空
                    </button>
                </div>
            </div>

            <!-- 加载动画 -->
            <div class="loading" id="loading">
                <div class="spinner"></div>
                <p>正在处理数据...</p>
            </div>

            <!-- 统计信息 -->
            <div id="statsSection" style="display: none;">
                <div class="stats">
                    <div class="stat-card">
                        <div class="stat-number" id="provinceCount">0</div>
                        <div class="stat-label">省级行政区</div>
                    </div>
                    <div class="stat-card">
                        <div class="stat-number" id="cityCount">0</div>
                        <div class="stat-label">地级市</div>
                    </div>
                    <div class="stat-card">
                        <div class="stat-number" id="districtCount">0</div>
                        <div class="stat-label">区县</div>
                    </div>
                    <div class="stat-card">
                        <div class="stat-number" id="totalCount">0</div>
                        <div class="stat-label">总计</div>
                    </div>
                </div>
            </div>

            <!-- 步骤2：下载SQL -->
            <div class="step" id="resultSection" style="display: none;">
                <div class="step-title">
                    <span class="step-number">2</span>
                    生成的SQL语句
                </div>
                <div class="input-group">
                    <label>复制下面的SQL或点击下载按钮：</label>
                    <textarea id="sqlOutput" readonly></textarea>
                </div>
                <div class="button-group">
                    <button class="btn-success" onclick="downloadSQL()">
                        💾 下载SQL文件
                    </button>
                    <button class="btn-primary" onclick="copySQL()">
                        📋 复制到剪贴板
                    </button>
                </div>
            </div>

            <!-- 步骤3：导入说明 -->
            <div class="step" id="instructionsSection" style="display: none;">
                <div class="step-title">
                    <span class="step-number">3</span>
                    导入数据库
                </div>
                <div class="alert alert-info">
                    <strong>💡 使用方法：</strong>
                    <ol style="margin-top: 10px; margin-left: 20px;">
                        <li>保存下载的SQL文件为 <code>regions_complete.sql</code></li>
                        <li>打开命令行/终端</li>
                        <li>执行命令：<code>psql -d yuelao_db -f regions_complete.sql</code></li>
                        <li>等待导入完成</li>
                    </ol>
                </div>
            </div>
        </div>
    </div>

    <script>
        // 加载示例数据
        function loadSample() {
            // 树形结构示例（GitHub格式）
            const sampleTree = [
                {
                    "code": "11",
                    "name": "北京市",
                    "children": [
                        {
                            "code": "1101",
                            "name": "市辖区",
                            "children": [
                                {"code": "110101", "name": "东城区"},
                                {"code": "110102", "name": "西城区"},
                                {"code": "110105", "name": "朝阳区"}
                            ]
                        }
                    ]
                },
                {
                    "code": "44",
                    "name": "广东省",
                    "children": [
                        {
                            "code": "4401",
                            "name": "广州市",
                            "children": [
                                {"code": "440103", "name": "荔湾区"},
                                {"code": "440104", "name": "越秀区"}
                            ]
                        }
                    ]
                }
            ];
            
            document.getElementById('jsonInput').value = JSON.stringify(sampleTree, null, 2);
            showSuccess('已加载树形结构示例数据（GitHub格式），点击"解析并转换"按钮查看效果');
        }
        
        // 清空输入
        function clearInput() {
            document.getElementById('jsonInput').value = '';
            document.getElementById('sqlOutput').value = '';
            document.getElementById('statsSection').style.display = 'none';
            document.getElementById('resultSection').style.display = 'none';
            document.getElementById('instructionsSection').style.display = 'none';
        }

        // 解析JSON并生成SQL
        function parseJSON() {
            const jsonInput = document.getElementById('jsonInput').value.trim();
            
            if (!jsonInput) {
                showError('请先粘贴JSON数据！');
                return;
            }

            // 显示加载动画
            document.getElementById('loading').classList.add('active');

            setTimeout(() => {
                try {
                    const data = JSON.parse(jsonInput);
                    
                    // 验证数据格式
                    if (typeof data !== 'object' || data === null) {
                        throw new Error('数据格式错误：期望是一个对象');
                    }
                    
                    if (Object.keys(data).length === 0) {
                        throw new Error('数据为空，请检查JSON内容');
                    }
                    
                    const sql = convertToSQL(data);
                    
                    // 显示结果
                    document.getElementById('sqlOutput').value = sql;
                    document.getElementById('resultSection').style.display = 'block';
                    document.getElementById('instructionsSection').style.display = 'block';
                    
                    showSuccess('转换成功！共处理 ' + Object.keys(data).length + ' 条数据');
                    
                    // 滚动到结果
                    document.getElementById('resultSection').scrollIntoView({ behavior: 'smooth' });
                } catch (error) {
                    console.error('转换错误：', error);
                    showError('转换失败：' + error.message + '\n\n请确保粘贴的是正确的JSON格式数据。\n正确格式示例：{"110000":"北京市","120000":"天津市"}');
                } finally {
                    document.getElementById('loading').classList.remove('active');
                }
            }, 500);
        }
        
        // 显示错误信息
        function showError(message) {
            const alert = document.createElement('div');
            alert.className = 'alert alert-error';
            alert.innerHTML = '<strong>❌ 错误</strong><br>' + message.replace(/\n/g, '<br>');
            
            const content = document.querySelector('.content');
            content.insertBefore(alert, content.firstChild);
            
            setTimeout(() => alert.remove(), 8000);
            
            // 滚动到顶部
            window.scrollTo({ top: 0, behavior: 'smooth' });
        }
        
        // 显示成功信息
        function showSuccess(message) {
            const alert = document.createElement('div');
            alert.className = 'alert alert-success';
            alert.innerHTML = '<strong>✅ 成功</strong><br>' + message;
            
            const content = document.querySelector('.content');
            content.insertBefore(alert, content.firstChild);
            
            setTimeout(() => alert.remove(), 5000);
        }

        // 转换为SQL
        function convertToSQL(data) {
            const regions = [];
            let provinceCount = 0, cityCount = 0, districtCount = 0;

            // 判断数据格式
            const isTreeStructure = Array.isArray(data) || (data[Object.keys(data)[0]] && typeof data[Object.keys(data)[0]] === 'object' && data[Object.keys(data)[0]].children);
            
            if (isTreeStructure) {
                console.log('检测到树形结构数据，使用递归解析');
                
                // 处理树形结构（嵌套的children）
                const processNode = (node, parentCode, currentLevel) => {
                    if (!node || !node.code || !node.name) {
                        console.warn('跳过无效节点：', node);
                        return;
                    }
                    
                    const cleanCode = String(node.code).padEnd(12, '0');
                    const name = String(node.name).replace(/'/g, "''");
                    
                    // 统计
                    if (currentLevel === 1) provinceCount++;
                    else if (currentLevel === 2) cityCount++;
                    else if (currentLevel === 3) districtCount++;
                    
                    regions.push({
                        code: cleanCode,
                        parentCode: parentCode,
                        name: name,
                        level: currentLevel
                    });
                    
                    // 递归处理子节点
                    if (node.children && Array.isArray(node.children)) {
                        node.children.forEach(child => {
                            processNode(child, cleanCode, currentLevel + 1);
                        });
                    }
                };
                
                // 开始处理（如果是数组，遍历数组；如果是对象，遍历values）
                const dataArray = Array.isArray(data) ? data : Object.values(data);
                dataArray.forEach(province => {
                    processNode(province, null, 1);
                });
                
            } else {
                console.log('检测到扁平键值对数据');
                
                // 处理扁平结构（原有逻辑）
                for (const [code, value] of Object.entries(data)) {
                    let name;
                    if (typeof value === 'string') {
                        name = value;
                    } else if (typeof value === 'object' && value !== null) {
                        name = value.name || value.label || String(value);
                    } else {
                        name = String(value);
                    }
                    
                    if (!name || name === 'undefined' || name === 'null') {
                        console.warn(`跳过无效数据：code=${code}, value=`, value);
                        continue;
                    }
                    
                    const cleanCode = code.padEnd(12, '0');
                    let level = 0;
                    let parentCode = null;

                    if (cleanCode.endsWith('0000000000')) {
                        level = 1;
                        provinceCount++;
                    } else if (cleanCode.endsWith('00000000')) {
                        level = 2;
                        parentCode = cleanCode.substring(0, 2) + '0000000000';
                        cityCount++;
                    } else {
                        level = 3;
                        parentCode = cleanCode.substring(0, 4) + '00000000';
                        districtCount++;
                    }

                    regions.push({
                        code: cleanCode,
                        parentCode: parentCode,
                        name: String(name).replace(/'/g, "''"),
                        level: level
                    });
                }
            }

            // 更新统计
            document.getElementById('provinceCount').textContent = provinceCount;
            document.getElementById('cityCount').textContent = cityCount;
            document.getElementById('districtCount').textContent = districtCount;
            document.getElementById('totalCount').textContent = regions.length;
            document.getElementById('statsSection').style.display = 'block';

            // 生成SQL
            let sql = `-- ============================================
-- 中国行政区划完整数据（自动生成）
-- 生成时间：${new Date().toLocaleString('zh-CN')}
-- 总计：${regions.length} 条数据
-- 省级：${provinceCount} | 地级市：${cityCount} | 区县：${districtCount}
-- ============================================

SET client_encoding = 'UTF8';

TRUNCATE TABLE regions CASCADE;

`;

            // 按级别分组
            for (let level = 1; level <= 3; level++) {
                const levelRegions = regions.filter(r => r.level === level);
                if (levelRegions.length === 0) continue;

                const levelName = {1: '省级', 2: '地级市', 3: '区县'}[level];
                sql += `-- ${levelName}行政区 (${levelRegions.length}个)\n`;
                sql += `INSERT INTO regions (id, parent_id, name, level, code, sort_order) VALUES\n`;

                levelRegions.forEach((region, index) => {
                    const parentStr = region.parentCode ? `'${region.parentCode}'` : 'NULL';
                    const comma = index < levelRegions.length - 1 ? ',' : ';';
                    sql += `('${region.code}', ${parentStr}, '${region.name}', ${level}, '${region.code}', ${index + 1})${comma}\n`;
                });

                sql += '\n';
            }

            sql += `-- 验证数据
SELECT '省级行政区：' || COUNT(*) || ' 个' FROM regions WHERE level = 1;
SELECT '地级市：' || COUNT(*) || ' 个' FROM regions WHERE level = 2;
SELECT '区县：' || COUNT(*) || ' 个' FROM regions WHERE level = 3;
SELECT '总计：' || COUNT(*) || ' 条' FROM regions;

COMMIT;
`;

            return sql;
        }

        // 下载SQL文件
        function downloadSQL() {
            const sql = document.getElementById('sqlOutput').value;
            const blob = new Blob([sql], { type: 'text/plain;charset=utf-8' });
            const url = URL.createObjectURL(blob);
            const a = document.createElement('a');
            a.href = url;
            a.download = 'regions_complete_' + new Date().getTime() + '.sql';
            document.body.appendChild(a);
            a.click();
            document.body.removeChild(a);
            URL.revokeObjectURL(url);

            // 显示成功提示
            const alert = document.createElement('div');
            alert.className = 'alert alert-success';
            alert.innerHTML = '<strong>✅ 下载成功！</strong> 文件已保存，请导入到数据库。';
            document.getElementById('resultSection').insertBefore(alert, document.getElementById('resultSection').firstChild);
            setTimeout(() => alert.remove(), 3000);
        }

        // 复制到剪贴板
        function copySQL() {
            const sqlOutput = document.getElementById('sqlOutput');
            sqlOutput.select();
            document.execCommand('copy');

            // 显示成功提示
            const alert = document.createElement('div');
            alert.className = 'alert alert-success';
            alert.innerHTML = '<strong>✅ 复制成功！</strong> SQL已复制到剪贴板。';
            document.getElementById('resultSection').insertBefore(alert, document.getElementById('resultSection').firstChild);
            setTimeout(() => alert.remove(), 3000);
        }
    </script>
</body>
</html>

